package dao.Impl;

import dao.BaseDao;
import dao.CategoryDao;
import model.entity.Category;
import util.NowTime;

import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.*;

public class CategoryDaoImpl extends BaseDao implements CategoryDao {
    @Override
    public List<Category> Find(int type) {
        List<Category> categoryList=new ArrayList<>();
        String sql="select * from category where type=?;";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,type);
            //System.out.println(name);
            pstmt.executeQuery();
            rs= pstmt.getResultSet();
            while (rs.next()){
                Category category = new Category();
                category.setId(rs.getInt("id"));
                category.setName(rs.getString("name"));
                category.setType(rs.getInt("type"));
                category.setSort(rs.getInt("sort"));
                category.setStatus(rs.getInt("status"));
                category.setCreate_time(rs.getString("create_time"));
                category.setUpdate_time(rs.getString("update_time"));
                category.setCreate_user(rs.getInt("create_user"));
                category.setUpdate_user(rs.getInt("update_user"));
                categoryList.add(category);
            }
        }
        catch (SQLException e){
            System.out.println("按id查询分类出错!");
        }
        return categoryList;
    }

    @Override
    public void Del(int id) {
        String sql="delete from category where id=?;";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            pstmt.executeUpdate();
            System.out.println("删除分类成功!");
        }
        catch (SQLException e){
            System.out.println("删除分类失败!"+e);
        }
    }

    @Override
    public void UpdateSta(int status,int id) {
        String sql="update category set status=? where id=?;";
        try{
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,status);
            pstmt.setInt(2,id);
            pstmt.executeUpdate();
            System.out.println("分类状态修改成功!");
        }
        catch (SQLException e){
            System.out.println("分类状态修改失败!"+e);
        }
    }

    @Override
    public int FindSta(int id) {
        int status=1;
        String sql="select status from category where id=?;";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,id);
            pstmt.executeQuery();
            rs= pstmt.getResultSet();
            while (rs.next()){
                status=rs.getInt("status");
            }
            System.out.println("查询分类状态成功!");
        }
        catch (SQLException e){
            System.out.println("查询分类状态失败!"+e);
        }
        return status;
    }

    @Override
    public void UpdateCate(int id,String name,int sort,int user) {
        NowTime time=new NowTime();
        String t=time.getTime();
        String sql="update category set name=?,sort=?,status=?,update_time=?,update_user=? where id=?;";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,name);
            pstmt.setInt(2,sort);
            pstmt.setInt(3,1);
            pstmt.setTimestamp(4, Timestamp.valueOf(t));
            pstmt.setInt(5,user);
            pstmt.setInt(6,id);
            pstmt.executeUpdate();
            System.out.println("修改分类成功!");
        }
        catch (SQLException e){
            System.out.println("修改分类失败!"+e);
        }
    }

    @Override
    public void CreateCate(String name, int sort,int type,int user) {
        NowTime time=new NowTime();
        String t=time.getTime();
        String sql="insert into category (name,type,sort, status, create_time,create_user,update_time,update_user) value(?,?,?,?,?,?,?,?);";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setString(1,name);
            pstmt.setInt(2,type);
            pstmt.setInt(3,sort);
            pstmt.setInt(4,1);
            pstmt.setTimestamp(5, Timestamp.valueOf(t));
            pstmt.setInt(6,user);
            pstmt.setTimestamp(7,Timestamp.valueOf(t));
            pstmt.setInt(8,user);
            pstmt.executeUpdate();
            System.out.println("新增分类成功!");
        }
        catch (SQLException e){
            System.out.println("新增分类失败!"+e);
        }
    }

    @Override
    public List<Category> Page(int page, int pageSize,String name,Integer type) {
        List<Category> categoryList=new ArrayList<>();
        String sql="select * from category where 1=1";
        if(name!=null&&name!=""){
            sql+=" and name like concat('%',"+"'"+name+"'"+",'%')";
        }
        if(type!=null){
            sql+=" and type="+type;
        }
        page=(page-1)*pageSize;
        sql+=" limit ?,?;";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.setInt(1,page);
            pstmt.setInt(2,pageSize);
            pstmt.executeQuery();
            rs= pstmt.getResultSet();
            while (rs.next()){
                Category c=new Category();
                c.setId(rs.getInt("id"));
                c.setName(rs.getString("name"));
                c.setType(rs.getInt("type"));
                c.setStatus(rs.getInt("status"));
                c.setSort(rs.getInt("sort"));
                c.setCreate_user(rs.getInt("create_user"));
                c.setUpdate_user(rs.getInt("update_user"));
                c.setUpdate_time(String.valueOf(rs.getTimestamp("update_time")));
                c.setCreate_time(String.valueOf(rs.getTimestamp("create_time")));
                categoryList.add(c);
            }
            System.out.println("分页查询成功!");
        }
        catch (SQLException e){
            System.out.println("分页查询失败!"+e);
        }
        return categoryList;
    }

    @Override
    public List<Category> All() {
        List<Category> categoryList=new ArrayList<>();
        String sql="select * from category;";
        try {
            pstmt=conn.prepareStatement(sql);
            pstmt.executeQuery();
            rs= pstmt.getResultSet();
            while (rs.next()){
                Category c=new Category();
                c.setId(rs.getInt("id"));
                c.setName(rs.getString("name"));
                c.setStatus(rs.getInt("status"));
                c.setSort(rs.getInt("sort"));
                c.setCreate_user(rs.getInt("create_user"));
                c.setUpdate_user(rs.getInt("update_user"));
                c.setUpdate_time(String.valueOf(rs.getTimestamp("update_time")));
                c.setCreate_time(String.valueOf(rs.getTimestamp("create_time")));
                categoryList.add(c);
            }
            System.out.println("查询所有成功!");
        }
        catch (SQLException e){
            System.out.println("查询所有失败!"+e);
        }
        return categoryList;
    }

    @Override
    public List<Category> byType(String type) {
        List<Category> categoryList=new ArrayList<>();
        String sql="select * from category where 1=1";
        if(type!=null&& !type.isEmpty()){
            int type1=Integer.parseInt(type);
            sql+=" and type="+type1;
        }
        try{
            pstmt=conn.prepareStatement(sql);
            pstmt.executeQuery();
            rs= pstmt.getResultSet();
            while (rs.next()){
                Category c=new Category();
                c.setId(rs.getInt("id"));
                c.setName(rs.getString("name"));
                c.setSort(rs.getInt("sort"));
                c.setType(rs.getInt("type"));
                c.setCreate_time(String.valueOf(rs.getTimestamp("create_time")));
                c.setUpdate_time(String.valueOf(rs.getTimestamp("update_time")));
                c.setUpdate_user(rs.getInt("update_user"));
                c.setCreate_user(rs.getInt("create_user"));
                c.setStatus(rs.getInt("status"));
                categoryList.add(c);
            }
            System.out.println("按type查询分类成功!");
        }
        catch (SQLException e){
            System.out.println("按type查询分类失败!"+e);
        }
        return categoryList;
    }
}
